﻿Imports SS
Imports SS.Databases
Imports SS.Databases.Connecter
Imports SS.ApplicationBlock

Public Class Officer

	Dim connOra As DBOracle
	Public ReadOnly Property CurrentConnection As DBOracle
		Get
			Return connOra
		End Get
	End Property

	Public Sub New()

	End Sub

	Public Sub New(ByVal Transaction As Boolean)
		connOra = New DBOracle(Common.Utility.ConnectString, Transaction)
	End Sub

	Public Shared Function NewId() As String
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Return connOraSelect.ExecuteScalar("SELECT " & Common.Utility.Schema & ".RWN_SEQ_OFFICER_ID.NEXTVAL FROM DUAL")
		End Using
	End Function

    Private Shared Function Excute(ByVal sql As String) As DataTable
        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Return connOraSelect.GetDataTable(sql, "Data")
        End Using
    End Function

	Public Function Insert(ByVal info As Entity.OfficerInfoItem) As Boolean
		Dim strSql As String
		strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
			Return True
		Else
			Return False
		End If
	End Function

	Public Function Update(ByVal info As Entity.OfficerInfoItem) As Boolean
		Dim strSql As String
		strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO", "OFFICER_ID='" & info.officer_id & "'")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
			Return True
		Else
			Return False
		End If
	End Function

    Public Function UpdateOfficerIncome(ByVal info As Entity.OfficerIncome) As Boolean
        Dim strsql As New Text.StringBuilder
        With info
            strsql.AppendLine("UPDATE " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME SET")
            strsql.AppendLine("INCOME_PAID_FLAG='" & .income_paid_flag & "',")
            strsql.AppendLine("UPD_USER_ID='" & .upd_user_id & "',")
            strsql.AppendLine("UPD_DATE=sysdate")
            strsql.AppendLine("WHERE SUMMARY_ID=" & .summary_id)
        End With

        Try
            If connOra.ExecuteNonQuery(strsql.ToString) = 1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Common.LogError.AddLogException(ex)
            Return False
        End Try
    End Function

    Public Function InsertOfficerIncomeHistory(ByVal infoid As String) As Boolean
        Dim strsql As New Text.StringBuilder
        strsql.AppendLine("INSERT INTO " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME_PAID_H ")
        strsql.AppendLine("(SUMMARY_ID, OFFICER_ID, INCOME_PAID_FLAG, UPD_USER_ID, UPD_DATE) ")
        strsql.AppendLine("SELECT SUMMARY_ID, OFFICER_ID, INCOME_PAID_FLAG, UPD_USER_ID, UPD_DATE ")
        strsql.AppendLine("FROM " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME ")
        strsql.AppendLine("WHERE SUMMARY_ID=" & infoid)
        Try
            If connOra.ExecuteNonQuery(strsql.ToString) = 1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Common.LogError.AddLogException(ex)
            Return False
        End Try
    End Function

	Public Function AddHistoryLog(ByVal officerId As String) As Boolean

		Try
			connOra.ExecuteNonQuery("INSERT INTO " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO_HISTORY SELECT " & Common.Utility.Schema & ".RNW_SEQ_HISTORY_ID.NEXTVAL, RNWM_MAIN_OFFICER_INFO.* FROM  " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & officerId)
			Return True
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	Public Function ClearDownline(ByVal officerId As String) As Boolean

		Try
			connOra.ExecuteNonQuery("UPDATE " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO SET OFFICER_DOWNLINE_LEFT = NULL WHERE OFFICER_DOWNLINE_LEFT = " & officerId)
			connOra.ExecuteNonQuery("UPDATE " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO SET OFFICER_DOWNLINE_RIGHT = NULL WHERE OFFICER_DOWNLINE_RIGHT = " & officerId)
			Return True
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	Public Function UpdateDownLine(ByVal uplineId As String, ByVal downlineId As String, ByVal downlineLR As Char) As Boolean

		Dim sb As New Text.StringBuilder

		sb.AppendLine("Update " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("SET ")
		If downlineLR.ToString.Equals("L") Then
			sb.AppendLine("OFFICER_DOWNLINE_LEFT='" & downlineId & "'")
		Else
			sb.AppendLine("OFFICER_DOWNLINE_RIGHT='" & downlineId & "'")
		End If
		sb.AppendLine("WHERE OFFICER_ID = '" & uplineId & "'")

		Try
			If connOra.ExecuteNonQuery(sb.ToString) = 1 Then
				Return True
			Else
				Return False
			End If
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	Public Function DeleteLogDownLine(ByVal officer_id As String) As Boolean

		Dim strsql As New Text.StringBuilder
		strsql.AppendLine("DELETE FROM " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_DOWNLINE ")
		strsql.AppendLine("WHERE OFFICER_ID=" & officer_id)

		Try
			connOra.ExecuteNonQuery(strsql.ToString)
			Return True
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	Public Function UpdateLogDownLine(ByVal log As Entity.MainOfficerDownlineItem) As Boolean

		Dim strsql As New Text.StringBuilder
		With log
			strsql.AppendLine("UPDATE " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_DOWNLINE SET")
			strsql.AppendLine("SPONSOR_ID='" & .sponsor_id & "',")
			strsql.AppendLine("UPD_USER_ID='" & .upd_user_id & "',")
			strsql.AppendLine("UPD_DATE=sysdate")
			strsql.AppendLine("WHERE OFFICER_ID=" & .officer_id)
		End With

		Try
			If connOra.ExecuteNonQuery(strsql.ToString) = 1 Then
				Return True
			Else
				Return False
			End If
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	''' <summary>
	''' ในเดือนที่ต้องการค้นหามีการจ่ายเงินหรือยัง
	''' </summary>
	''' <param name="officerId"></param>
	''' <param name="yyyymm"></param>
	''' <returns></returns>
	''' <remarks></remarks>
	Public Shared Function isPaidInMonth(ByVal officerId As String, ByVal yyyymm As String) As Boolean
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim strRslt As String = connOraSelect.ExecuteScalar("SELECT NVL(COUNT(OFFICER_STATUS),0) FROM " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME WHERE INCOME_RV > 0 AND TO_CHAR(CAL_DATE,'yyyymm') = '" & yyyymm & "' AND OFFICER_ID='" & officerId & "'")
			If String.IsNullOrEmpty(strRslt) Or strRslt.Equals("0") Then
				Return False
			Else
				Return True
			End If
		End Using
	End Function

	Public Function RenewOfficer(ByVal renewInfo As Entity.RememerInfoItem) As Boolean
		Dim strsql As New Text.StringBuilder
		With renewInfo
			strsql.AppendLine("INSERT INTO " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_REMEMBER")
			strsql.AppendLine("(REMEMBER_ID,OFFICER_ID,REMEMBER_DATE,REMEMBER_REFERENCE,RECORD_STATUS,CREATE_USER_ID,CREATE_DATE,UPD_USER_ID,UPD_DATE)")
			strsql.AppendLine("VALUES (")
			strsql.AppendLine("RNW_SEQ_REMEMBER.NEXTVAL,")
			strsql.AppendLine("'" & .officer_id & "',")
			strsql.AppendLine("SYSDATE,")
			strsql.AppendLine("'UPDATE ONLINE BY STAFF',")
			strsql.AppendLine("'A',")
			strsql.AppendLine("'" & .create_user_id & "',")
			strsql.AppendLine("SYSDATE,")
			strsql.AppendLine("'" & .create_user_id & "',")
			strsql.AppendLine("SYSDATE")
			strsql.AppendLine(")")
		End With
		Try
			If connOra.ExecuteNonQuery(strsql.ToString) = 1 Then
				Return True
			Else
				Return False
			End If
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try
	End Function

	Public Function InsertLogDownLine(ByVal log As Entity.MainOfficerDownlineItem) As Boolean

		Dim strsql As New Text.StringBuilder
		With log
			strsql.AppendLine("INSERT INTO " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_DOWNLINE (")
			strsql.AppendLine("DOWNLINE_ID,")
			strsql.AppendLine("OFFICER_ID,")
			strsql.AppendLine("SPONSOR_ID,")
			strsql.AppendLine("DOWNLINE_YEAR,")
			strsql.AppendLine("RECORD_STATUS,")
			strsql.AppendLine("CREATE_USER_ID,")
			strsql.AppendLine("CREATE_DATE")
			strsql.AppendLine(")")
			strsql.AppendLine(" VALUES ")
			strsql.AppendLine("(")
			strsql.AppendLine("" & Common.Utility.Schema & ".RWN_SEQ_DOWNLINE_ID.NEXTVAL,")
			strsql.AppendLine("'" & .officer_id & "',")
			strsql.AppendLine("'" & .sponsor_id & "',")
			strsql.AppendLine("to_char(sysdate,'YYYY'),")
			strsql.AppendLine("'A',")
			strsql.AppendLine("'" & .create_user_id & "',")
			strsql.AppendLine("sysdate")
			strsql.AppendLine(")")
		End With

		Try
			If connOra.ExecuteNonQuery(strsql.ToString) = 1 Then
				Return True
			Else
				Return False
			End If
		Catch ex As Exception
			Common.LogError.AddLogException(ex)
			Return False
		End Try

	End Function

	Public Function InsertUserInfo(ByVal userinfo As Entity.UserInfoItem) As Boolean

		Dim strSql As String
		strSql = ORM.Broker.RetrieveInsert(userinfo, Common.Utility.Schema & ".RNWM_MAIN_USER_INFO")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
			Return True
		Else
			Return False
		End If

	End Function

	Public Function UpdateUserInfoHaveTransaction(ByVal userinfo As Entity.UserInfoItem, ByVal offcier_id As String) As Boolean
		Dim strSql As String
		strSql = ORM.Broker.RetrieveUpdate(userinfo, Common.Utility.Schema & ".RNWM_MAIN_USER_INFO", " OFFCIER_ID = '" & offcier_id & "'")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
			Return True
		Else
			Return False
		End If
	End Function

	Public Shared Function UpdateUserInfo(ByVal userinfo As Entity.UserInfoItem, ByVal offcier_id As String) As Boolean
		Using connOraUpdate As New DBOracle(Common.Utility.ConnectString, False)
			Dim strSql As String
			strSql = ORM.Broker.RetrieveUpdate(userinfo, Common.Utility.Schema & ".RNWM_MAIN_USER_INFO", " OFFCIER_ID = '" & offcier_id & "'")
			If connOraUpdate.ExecuteNonQuery(strSql) = 1 Then
				Return True
			Else
				Return False
			End If
		End Using
	End Function

	Public Sub Commit()
		connOra.Commit()
		connOra.Close()
	End Sub

	Public Sub RollBack()
		connOra.Rollback()
		connOra.Close()
	End Sub

	Public Shared Function GetOfficerId(ByVal officerAppId As String) As String

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar("select OFFICER_ID from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO Where OFFICER_APP_ID = '" & officerAppId & "'")
		End Using

	End Function

	Public Shared Function CheckDupAppID(ByVal officerAppId As String) As Integer
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar("Select Count(OFFICER_APP_ID) from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO Where OFFICER_APP_ID = '" & officerAppId & "'")
		End Using
	End Function

	Public Shared Function CheckDupAppTel(ByVal appTel As String, ByVal ignorOfficrID As String) As Boolean
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar("Select Count(OFFCIER_APP_TEL) from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO Where OFFCIER_APP_TEL = '" & appTel & "'" & IIf(String.IsNullOrEmpty(ignorOfficrID), "", " AND OFFICER_ID != " & ignorOfficrID))
		End Using
	End Function

	Public Shared Function CheckDupCardNo(ByVal cardNo As String, ByVal ignorOfficrID As String) As Boolean
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar("Select Count(OFFICER_CARD_NO) from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO Where OFFICER_CARD_NO = '" & cardNo & "'" & IIf(String.IsNullOrEmpty(ignorOfficrID), "", " AND OFFICER_ID != " & ignorOfficrID))
		End Using
	End Function

	Public Shared Function LoadOfficer(ByVal officerId As String) As DataTable

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.GetDataTable("select * from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO Where OFFICER_ID = '" & officerId & "'", "RNWM_MAIN_OFFICER_INFO")
		End Using

	End Function

	Public Shared Function LoadOfficer(ByVal NameLastTh As String, ByVal NameLastEn As String, ByVal MemberDateForm As String, ByVal MemberDateTo As String, ByVal inputerId As String, ByVal appId As String, ByVal appTel As String, ByVal cardNo As String) As DataTable

		Dim sb As New Text.StringBuilder

		If Not String.IsNullOrEmpty(appId) Then
			sb.AppendLine("And OFFICER_APP_ID = '" & DBUtility.ReplaceQ(appId) & "'")
		End If

		If Not String.IsNullOrEmpty(appTel) Then
			sb.AppendLine("And OFFCIER_APP_TEL = '" & DBUtility.ReplaceQ(appTel) & "'")
		End If

		If Not String.IsNullOrEmpty(cardNo) Then
			sb.AppendLine("And OFFICER_CARD_NO = '" & DBUtility.ReplaceQ(cardNo) & "'")
		End If

		If Not String.IsNullOrEmpty(inputerId) Then
			sb.AppendLine("And (OFFICER_INPUTER = '" & DBUtility.ReplaceQ(inputerId) & "'")
			sb.AppendLine("OR OFFICER_SPONSOR = '" & DBUtility.ReplaceQ(inputerId) & "')")
		End If

		If Not String.IsNullOrEmpty(NameLastTh) Then
			Try
				If NameLastTh.Contains(" ") Then
					sb.AppendLine("And (OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh.Split(" ")(0)) & "%' OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh.Split(" ")(1)) & "%')")
				Else
					sb.AppendLine("And (OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh) & "%' OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh) & "%')")
				End If
			Catch ex As Exception
				sb.AppendLine("And (OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh) & "%' OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(NameLastTh) & "%')")
			End Try
		End If

		If Not String.IsNullOrEmpty(NameLastEn) Then
			Try
				If NameLastEn.Contains(" ") Then
					sb.AppendLine("And (OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn.Split(" ")(0)) & "%' OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn.Split(" ")(1)) & "%')")
				Else
					sb.AppendLine("And (OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn) & "%' OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn) & "%')")
				End If
			Catch ex As Exception
				sb.AppendLine("And (OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn) & "%' OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(NameLastEn) & "%')")
			End Try
		End If

		If (Not String.IsNullOrEmpty(MemberDateForm)) And (String.IsNullOrEmpty(MemberDateTo)) Then
			sb.AppendLine("And TO_DATE(TO_CHAR(OFFCIER_MEMBER_DATE,'dd/mm/yyyy'),'dd/mm/yyyy') <= TO_DATE('" & MemberDateForm & "','dd/mm/yyyy')")
		End If

		If (Not String.IsNullOrEmpty(MemberDateTo)) And (String.IsNullOrEmpty(MemberDateForm)) Then
			sb.AppendLine("And TO_DATE(TO_CHAR(OFFCIER_MEMBER_DATE,'dd/mm/yyyy'),'dd/mm/yyyy') >= TO_DATE('" & MemberDateTo & "','dd/mm/yyyy')")
		End If

		If (Not String.IsNullOrEmpty(MemberDateTo)) And (Not String.IsNullOrEmpty(MemberDateForm)) Then
			sb.AppendLine("And TO_DATE(TO_CHAR(OFFCIER_MEMBER_DATE,'dd/mm/yyyy'),'dd/mm/yyyy') BETWEEN TO_DATE('" & MemberDateForm & "','dd/mm/yyyy') AND TO_DATE('" & MemberDateTo & "','dd/mm/yyyy')")
		End If

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Return connOraSelect.GetDataTable("SELECT ROWNUM ORDERNO,OFFICER.* FROM (SELECT ROWNUM ORDERNO,OFFICER_APP_ID,OFFCIER_APP_TEL,OFFICER_FIRSTNAME_THA,OFFICER_LASTNAME_THA,OFFCIER_MEMBER_DATE,OFFICER_STATUS,OFFICER_TYPE,OFFICER_REMARK,OFFICER_ID FROM " & Common.Utility.Schema & ".RNW_VW_OFFICER WHERE RECORD_STATUS = 'A' " & sb.ToString & " ORDER BY OFFCIER_MEMBER_DATE ASC NULLS LAST ) OFFICER", "RNWM_MAIN_OFFICER_INFO")
		End Using

	End Function

	Public Shared Function LoadSponser(ByVal appId As String, ByVal officer_line_id As String) As List(Of Entity.OfficerInfoItem)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)

			Dim SearchDownline As String
			Dim strDownlineId As String = Nothing

			If Not String.IsNullOrEmpty(officer_line_id) Then
				SearchDownline = GetDownline(officer_line_id) 'connOraSelect.ExecuteScalar("SELECT RNWF_SEARCH_DOWNLINE(" & officer_line_id & ") FROM DUAL")
				strDownlineId = String.Empty
				For Each Str As String In SearchDownline.Split("|")
					If Not String.IsNullOrEmpty(Str) Then
						strDownlineId &= "RNW_VW_SPONSOR_SELECT.OFFICER_ID = " & Str & " OR "
					End If
				Next

				If (Not String.IsNullOrEmpty(strDownlineId)) AndAlso strDownlineId.Length > 4 Then
					strDownlineId = strDownlineId.Substring(0, strDownlineId.Length - 4)
				Else
					strDownlineId = String.Empty
				End If
			End If

			Dim sbSearch As New Text.StringBuilder
			sbSearch.AppendLine("select *")
			sbSearch.AppendLine("from " & Common.Utility.Schema & ".RNW_VW_SPONSOR_SELECT")
			sbSearch.AppendLine("where ")
			If Not String.IsNullOrEmpty(strDownlineId) Then
				sbSearch.AppendLine(" (" & strDownlineId & ") and (")
			Else
				sbSearch.AppendLine(" (")
			End If
			sbSearch.AppendLine("OFFICER_APP_ID = '" & DBUtility.ReplaceQ(appId) & "')")

			Dim dt As DataTable = connOraSelect.GetDataTable(sbSearch.ToString, "Upline")
			If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
				Dim objReturn As List(Of Entity.OfficerInfoItem)
				objReturn = New List(Of Entity.OfficerInfoItem)
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.OfficerInfoItem)(dt)
				Return objReturn
			Else
				Return Nothing
			End If

		End Using
	End Function

	Public Shared Function SearchSponser(ByVal keyword As String, ByVal officer_line_id As String) As DataTable

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)

			Dim SearchDownline As String
			Dim strDownlineId As String = Nothing

			If Not String.IsNullOrEmpty(officer_line_id) Then
				'SearchDownline = connOraSelect.ExecuteScalar("SELECT RNWF_SEARCH_DOWNLINE(" & officer_line_id & ") FROM DUAL")
				SearchDownline = GetDownline(officer_line_id)
				strDownlineId = String.Empty
				For Each Str As String In SearchDownline.Split("|")
					If Not String.IsNullOrEmpty(Str) Then
						strDownlineId &= "RNW_VW_SPONSOR_SELECT.OFFICER_ID = " & Str & " OR "
					End If
				Next

				If (Not String.IsNullOrEmpty(strDownlineId)) AndAlso strDownlineId.Length > 4 Then
					strDownlineId = strDownlineId.Substring(0, strDownlineId.Length - 4)
				Else
					strDownlineId = String.Empty
				End If
			End If
		
			Dim sbSearch As New Text.StringBuilder

			sbSearch.AppendLine("select *")
			sbSearch.AppendLine("from " & Common.Utility.Schema & ".RNW_VW_SPONSOR_SELECT")
			sbSearch.AppendLine("where ")
			If Not String.IsNullOrEmpty(strDownlineId) Then
				sbSearch.AppendLine(" (" & strDownlineId & ") and (")
			Else
				sbSearch.AppendLine(" (")
			End If
			sbSearch.AppendLine("OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%' ")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_APP_ID LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFCIER_APP_TEL LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%')")

			Return connOraSelect.GetDataTable(sbSearch.ToString, "Sponser")
		End Using

	End Function

	Public Shared Function SearchUpline(ByVal keyword As String, ByVal sponsorId As String) As DataTable

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim strDownlineId As String

			If String.IsNullOrEmpty(sponsorId) Then
				strDownlineId = String.Empty
			Else
				Dim SearchDownline As String '= connOraSelect.ExecuteScalar("SELECT RNWF_SEARCH_DOWNLINE(" & sponsorId & ") FROM DUAL")
				SearchDownline = GetDownline(sponsorId)

				strDownlineId = String.Empty
				For Each Str As String In SearchDownline.Split("|")
					If Not String.IsNullOrEmpty(Str) Then
						strDownlineId &= "RNW_VW_UPLINE_SELECT.OFFICER_ID = " & Str & " OR "
					End If
				Next

				If (Not String.IsNullOrEmpty(strDownlineId)) AndAlso strDownlineId.Length > 4 Then
					strDownlineId = strDownlineId.Substring(0, strDownlineId.Length - 4)
				Else
					strDownlineId = String.Empty
				End If
			End If

			Dim sbSearch As New Text.StringBuilder

			sbSearch.AppendLine("select *")
			sbSearch.AppendLine("from " & Common.Utility.Schema & ".RNW_VW_UPLINE_SELECT")
			sbSearch.AppendLine("where ")
			If Not String.IsNullOrEmpty(strDownlineId) Then
				sbSearch.AppendLine(" (" & strDownlineId & ") and (")
			Else
				sbSearch.AppendLine(" (")
			End If
			sbSearch.AppendLine(" OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%' ")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_APP_ID LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFCIER_APP_TEL LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%') ")

			Return connOraSelect.GetDataTable(sbSearch.ToString, "Upline")
		End Using

	End Function

	Public Shared Function LoadSender(ByVal appId As String, ByVal sponsorId As String) As List(Of Entity.OfficerInfoItem)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim strDownlineId As String

			If String.IsNullOrEmpty(sponsorId) Then
				strDownlineId = String.Empty
			Else
				Dim SearchDownline As String '= connOraSelect.ExecuteScalar("SELECT RNWF_SEARCH_DOWNLINE(" & sponsorId & ") FROM DUAL")
				SearchDownline = GetDownline(sponsorId)

				strDownlineId = String.Empty
				For Each Str As String In SearchDownline.Split("|")
					If Not String.IsNullOrEmpty(Str) Then
						strDownlineId &= "RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & Str & " OR "
					End If
				Next

				If (Not String.IsNullOrEmpty(strDownlineId)) AndAlso strDownlineId.Length > 4 Then
					strDownlineId = strDownlineId.Substring(0, strDownlineId.Length - 4)
				Else
					strDownlineId = String.Empty
				End If
			End If


			Dim sbSearch As New Text.StringBuilder
			sbSearch.AppendLine("select OFFICER_ID, OFFICER_APP_ID, OFFCIER_APP_TEL, ")
			sbSearch.AppendLine("OFFCIER_MEMBER_DATE, OFFICER_FIRSTNAME_THA, OFFICER_LASTNAME_THA,  ")
			sbSearch.AppendLine("OFFICER_FIRSTNAME_ENG, OFFICER_LASTNAME_ENG")
			sbSearch.AppendLine("from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
			sbSearch.AppendLine("where ")
			sbSearch.AppendLine("OFFICER_APP_ID = '" & DBUtility.ReplaceQ(appId) & "'")
			If Not String.IsNullOrEmpty(strDownlineId) Then
				sbSearch.AppendLine(" and (" & strDownlineId & ") ")
			End If

			Dim dt As DataTable = connOraSelect.GetDataTable(sbSearch.ToString, "Upline")
			If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
				Dim objReturn As List(Of Entity.OfficerInfoItem)
				objReturn = New List(Of Entity.OfficerInfoItem)
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.OfficerInfoItem)(dt)
				Return objReturn
			Else
				Return Nothing
			End If
		End Using
	End Function

	Public Shared Function SearchSender(ByVal keyword As String, ByVal sponsorId As String) As List(Of Entity.OfficerInfoItem)

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)

			Dim strDownlineId As String

			If String.IsNullOrEmpty(sponsorId) Then
				strDownlineId = String.Empty
			Else
				Dim SearchDownline As String '= connOraSelect.ExecuteScalar("SELECT RNWF_SEARCH_DOWNLINE(" & sponsorId & ") FROM DUAL")
				SearchDownline = GetDownline(sponsorId)

				strDownlineId = String.Empty
				For Each Str As String In SearchDownline.Split("|")
					If Not String.IsNullOrEmpty(Str) Then
						strDownlineId &= "RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & Str & " OR "
					End If
				Next

				If (Not String.IsNullOrEmpty(strDownlineId)) AndAlso strDownlineId.Length > 4 Then
					strDownlineId = strDownlineId.Substring(0, strDownlineId.Length - 4)
				Else
					strDownlineId = String.Empty
				End If
			End If

			Dim sbSearch As New Text.StringBuilder

			sbSearch.AppendLine("Select OFFICER_ID, OFFICER_APP_ID, OFFCIER_APP_TEL, ")
			sbSearch.AppendLine("OFFCIER_MEMBER_DATE, OFFICER_FIRSTNAME_THA, OFFICER_LASTNAME_THA,  ")
			sbSearch.AppendLine("OFFICER_FIRSTNAME_ENG, OFFICER_LASTNAME_ENG")
			sbSearch.AppendLine("from " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
			sbSearch.AppendLine("where ")

			If Not String.IsNullOrEmpty(strDownlineId) Then
				sbSearch.AppendLine(" (" & strDownlineId & ") and (")
			Else
				sbSearch.AppendLine(" (")
			End If
			sbSearch.AppendLine("OFFICER_FIRSTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%' ")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_THA LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_APP_ID LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFCIER_APP_TEL LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_FIRSTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%'")
			sbSearch.AppendLine("OR OFFICER_LASTNAME_ENG LIKE '%" & DBUtility.ReplaceQ(keyword) & "%')")

			Dim dt As DataTable = connOraSelect.GetDataTable(sbSearch.ToString, "Upline")
			If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
				Dim objReturn As List(Of Entity.OfficerInfoItem)
				objReturn = New List(Of Entity.OfficerInfoItem)
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.OfficerInfoItem)(dt)
				Return objReturn
			Else
				Return Nothing
			End If
		End Using

	End Function

	Public Shared Function IsActive(ByVal officerID As String) As Boolean
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			If String.IsNullOrEmpty(connOraSelect.ExecuteScalar("SELECT USER_ACTIVATED_DATE FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO WHERE OFFCIER_ID='" & officerID & "'")) Then
				Return False
			Else
				Return True
			End If
		End Using
	End Function

	Public Shared Sub UpdateActiveDate(ByVal officerID As String)
		Using connOra As New DBOracle(Common.Utility.ConnectString, False)
			connOra.ExecuteNonQuery("UPDATE " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO SET USER_ACTIVATED_DATE=sysdate WHERE OFFCIER_ID='" & officerID & "'")
		End Using
	End Sub

	Public Shared Function GetUserInfo(ByVal userId As String) As Entity.UserInfoItem
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim objReturn As List(Of Entity.UserInfoItem)
			objReturn = New List(Of Entity.UserInfoItem)

			Dim sb As New Text.StringBuilder
			sb.AppendLine("SELECT RNWM_MAIN_OFFICER_INFO.OFFICER_FIRSTNAME_THA || ' ' || RNWM_MAIN_OFFICER_INFO.OFFICER_LASTNAME_THA AS OFFICER_FULLNAME, RNWM_MAIN_USER_INFO.*, RNWM_MAIN_USER_ROLE.ROLE_ID FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE ")
			sb.AppendLine("ON RNWM_MAIN_USER_INFO.USER_ID = RNWM_MAIN_USER_ROLE.USER_ID")
			sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWM_MAIN_USER_INFO.OFFCIER_ID")
			sb.AppendLine("WHERE RNWM_MAIN_USER_INFO.RECORD_STATUS = 'A' AND RNWM_MAIN_USER_INFO.USER_ID = '" & SS.Databases.DBUtility.ReplaceQ(userId) & "'")

			Dim dt As DataTable = connOraSelect.GetDataTable(sb.ToString, "userinfo")
			If dt Is Nothing OrElse dt.Rows.Count = 0 Then
				Return Nothing
			Else
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.UserInfoItem)(dt)
				Return objReturn.Item(0)
			End If
		End Using
	End Function

	Public Shared Function GetUserInfoByOfficerId(ByVal officerID As String) As Entity.UserInfoItem
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim objReturn As List(Of Entity.UserInfoItem)
			objReturn = New List(Of Entity.UserInfoItem)
			Dim dt As DataTable = connOraSelect.GetDataTable("SELECT * FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO WHERE OFFCIER_ID = '" & SS.Databases.DBUtility.ReplaceQ(officerID) & "'", "userinfo")
			If dt Is Nothing OrElse dt.Rows.Count = 0 Then
				Return Nothing
			Else
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.UserInfoItem)(dt)
				Return objReturn.Item(0)
			End If
		End Using
	End Function

	Public Shared Function LoadResetEmail(ByVal appId As String, ByVal firstName As String, _
	 ByVal lastName As String, ByVal cardNo As String, ByVal appTel As String) As String

		appId = DBUtility.ReplaceQ(appId)
		firstName = DBUtility.ReplaceQ(firstName)
		lastName = DBUtility.ReplaceQ(lastName)
		cardNo = DBUtility.ReplaceQ(cardNo)
		appTel = DBUtility.ReplaceQ(appTel)

		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT NVL(MFO.OFFICER_MAIL_EMAIL,C.VALUE) RestEmail")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO MFO INNER JOIN " & Common.Utility.Schema & ".RNWM_CONFIG C ON 'ADMIN_MAIL' = C.KEY")
		sb.AppendLine("WHERE MFO.OFFICER_APP_ID = '" & appId & "' AND MFO.OFFICER_FIRSTNAME_THA = '" & firstName & "'")
		sb.AppendLine("AND MFO.OFFICER_LASTNAME_THA = '" & lastName & "'  AND MFO.OFFICER_CARD_NO = '" & cardNo & "' ")
		sb.AppendLine("AND MFO.OFFCIER_APP_TEL = '" & appTel & "'")
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar(sb.ToString)
		End Using
	End Function

	Public Shared Function LoadForReset(ByVal resetURL As String) As String
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT ")
		sb.AppendLine("OFFCIER_ID")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
		sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO")
		sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWM_MAIN_USER_INFO.OFFCIER_ID")
		sb.AppendLine("WHERE RNWM_MAIN_USER_INFO.USER_RESET_URL = '" & resetURL & "'")
		sb.AppendLine("AND RNWM_MAIN_USER_INFO.RECORD_STATUS = 'A'")
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar(sb.ToString)
		End Using
	End Function

	Public Shared Function LoadProfile(ByVal officerID As String) As Entity.OfficerInfoItem
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim objReturn As List(Of Entity.OfficerInfoItem)
			objReturn = New List(Of Entity.OfficerInfoItem)
			Dim dt As DataTable = connOraSelect.GetDataTable("SELECT * FROM " & Common.Utility.Schema & ".RNW_VW_OFFICER WHERE OFFICER_ID = '" & SS.Databases.DBUtility.ReplaceQ(officerID) & "'", "profileinfo")
			If dt Is Nothing OrElse dt.Rows.Count = 0 Then
				Return Nothing
			Else
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.OfficerInfoItem)(dt)
				Return objReturn.Item(0)
			End If
		End Using
	End Function

	Public Shared Function LoadSummaryInfo(ByVal officerID As String) As Entity.SumInfoItem
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim objReturn As List(Of Entity.SumInfoItem)
			objReturn = New List(Of Entity.SumInfoItem)
			Dim dt As DataTable = connOraSelect.GetDataTable("SELECT * FROM " & Common.Utility.Schema & ".RNW_VW_SUMMARY_INFO WHERE OFFICER_ID = '" & SS.Databases.DBUtility.ReplaceQ(officerID) & "'", "profileinfo")
			If dt Is Nothing OrElse dt.Rows.Count = 0 Then
				Return Nothing
			Else
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.SumInfoItem)(dt)
				Return objReturn.Item(0)
			End If
		End Using
	End Function

    Public Shared Function GetOfficerIncome(ByVal month As String, ByVal year As String, ByVal telSuccess As String, ByVal officerStatus As String, ByVal officerAppId As String, ByVal paidStatus As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.SUMMARY_ID, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_PAID_FLAG, ")
        sb.AppendLine("TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'DD/MM/YYYY') AS CAL_DATE, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID, ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_ID, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS, ")
        sb.AppendLine("RNWM_STATUS_MAPPING.STATUS_DESC, ")
        sb.AppendLine("RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
        sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWT_SUM_OFFICER_INCOME")
        sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWT_SUM_OFFICER_INCOME.OFFICER_ID")
        sb.AppendLine("LEFT OUTER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_BANK_INFO")
        sb.AppendLine("ON RNWM_MAIN_OFFICER_INFO.BANK_ID = RNWM_MAIN_BANK_INFO.BANK_ID")
        sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_STATUS_MAPPING")
        sb.AppendLine("ON RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS = RNWM_STATUS_MAPPING.STATUS_ID")

        sb.AppendLine("WHERE RNWT_SUM_OFFICER_INCOME.INCOME_TOTAL > 0 ")
        sb.AppendLine("AND TO_CHAR(RNWT_SUM_OFFICER_INCOME.CAL_DATE,'MMYYYY') = '" & month & year & "'")

        If Not String.IsNullOrEmpty(telSuccess) AndAlso telSuccess <> "" Then
            sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFCIER_APP_TEL LIKE '%" & telSuccess & "%'")
        End If

        If Not String.IsNullOrEmpty(officerStatus) AndAlso officerStatus <> "" Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.OFFICER_STATUS = '" & officerStatus & "'")
        End If

        If Not String.IsNullOrEmpty(officerAppId) AndAlso officerAppId <> "" Then
            sb.AppendLine("AND RNWM_MAIN_OFFICER_INFO.OFFICER_APP_ID = '" & officerAppId & "'")
        End If

        If Not String.IsNullOrEmpty(paidStatus) AndAlso paidStatus <> "" Then
            sb.AppendLine("AND RNWT_SUM_OFFICER_INCOME.INCOME_PAID_FLAG = '" & paidStatus & "'")
        End If

        Return Excute(sb.ToString)
    End Function

	Public Shared Function CheckUserInDownline(ByVal ownerOfficerId As String, ByVal OfficerAppId As String) As Boolean
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT " & Common.Utility.Schema & ".RNWF_SEARCH_USER_IN_DOWNLINE(" & ownerOfficerId & ",OFFICER_ID) FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO WHERE OFFICER_APP_ID = '" & OfficerAppId & "'")
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim strRslt As String = connOraSelect.ExecuteScalar(sb.ToString)
			If String.IsNullOrEmpty(strRslt) Then
				Return False
			Else
				If strRslt.Equals("0") Then
					Return False
				Else
					Return True
				End If
			End If
		End Using
	End Function

	Public Shared Function CheckDowlineLog(ByVal officerId As String) As Boolean
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT count(*) FROM " & Common.Utility.Schema & ".RNWT_MAIN_OFFICER_DOWNLINE WHERE  OFFICER_ID = " & officerId)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim strRslt As String = connOraSelect.ExecuteScalar(sb.ToString)
			If String.IsNullOrEmpty(strRslt) Then
				Return False
			Else
				If strRslt.Equals("0") Then
					Return False
				Else
					Return True
				End If
			End If
		End Using
	End Function

	Public Shared Function GetUpline(ByVal officerId As String) As String
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.ExecuteScalar("SELECT (SELECT A.OFFICER_APP_ID FROM RNWM_MAIN_OFFICER_INFO A WHERE A.OFFICER_ID =B.OFFICER_UPLINE) UPLINE_APP_ID FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO B WHERE  B.OFFICER_ID = " & officerId)
		End Using
	End Function

	Public Shared Function GetDownline(ByVal officerId As String, ByVal level As String) As String

		Dim dt As DataTable

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim sb As New Text.StringBuilder
			sb.AppendLine("SELECT")
			sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_ID , LEVEL AS LV,RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE,RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE_SIDE")
			sb.AppendLine("FROM")
			sb.AppendLine(Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
			sb.AppendLine("WHERE LEVEL =(" & level & "+1)")
			sb.AppendLine("START WITH")
			sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & officerId)
			sb.AppendLine("CONNECT BY")
			sb.AppendLine("PRIOR RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE")
			sb.AppendLine("ORDER BY LEVEL")

			dt = connOraSelect.GetDataTable(sb.ToString, "DL")
		End Using

		If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
			GetDownline = String.Empty
			For Each dr As DataRow In dt.Rows
				If Not String.IsNullOrEmpty(GetDownline) Then GetDownline &= "|"
				GetDownline &= dr("OFFICER_ID").ToString
			Next
		Else
			Return String.Empty
		End If

	End Function

	Public Shared Function GetDownline(ByVal officerId As String) As String

		Dim dt As DataTable

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim sb As New Text.StringBuilder
			sb.AppendLine("SELECT")
			sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_ID , LEVEL AS LV,RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE,RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE_SIDE")
			sb.AppendLine("FROM")
			sb.AppendLine(Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO")
			sb.AppendLine("START WITH")
			sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFICER_ID = " & officerId)
			sb.AppendLine("CONNECT BY")
			sb.AppendLine("PRIOR RNWM_MAIN_OFFICER_INFO.OFFICER_ID = RNWM_MAIN_OFFICER_INFO.OFFICER_UPLINE")
			sb.AppendLine("ORDER BY LEVEL")

			dt = connOraSelect.GetDataTable(sb.ToString, "DL")
		End Using

		If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
			GetDownline = String.Empty
			For Each dr As DataRow In dt.Rows
				If Not String.IsNullOrEmpty(GetDownline) Then GetDownline &= "|"
				GetDownline &= dr("OFFICER_ID").ToString
			Next
		Else
			Return String.Empty
		End If

	End Function

    Public Shared Function GetOfficerInfo(ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO ")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_INFO.OFFICER_ID = '" & officerId & "'")
        Return Excute(sb.ToString)
    End Function

#Region "Officer Payment"

    Public Shared Function GetOfficerPayment(ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_PAYMENT ")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_PAYMENT.OFFICER_ID = '" & officerId & "' ")
        sb.AppendLine("AND RNWM_MAIN_OFFICER_PAYMENT.RECORD_STATUS = 'Y' ")
        sb.AppendLine("ORDER BY PAYMENT_DATE DESC")
        Return Excute(sb.ToString)
    End Function

    Public Shared payID As Decimal
    Public Function InsertOfficerPayment(ByVal info As Entity.OfficerPayment) As Boolean
        info.payment_id = GetOfficerPaymentID()
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_PAYMENT")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function UpdateOfficerPayment(ByVal info As Entity.OfficerPayment) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_PAYMENT", "TO_CHAR(PAYMENT_ID)='" & info.payment_id & "'")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function GetOfficerPaymentID() As Decimal
        Dim RVID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select PAYMENT_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_PAYMENT ")
        sb.AppendLine("ORDER BY PAYMENT_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            RVID = Convert.ToDecimal(dt.Rows(0)("PAYMENT_ID")) + 1
        Else
            RVID = 1
        End If
        Return RVID
    End Function

#End Region


#Region "Officer Remember"

    Public Shared Function GetOfficerRemember(ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_REMEMBER ")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_REMEMBER.OFFICER_ID = '" & officerId & "' ")
        sb.AppendLine("AND RNWM_MAIN_OFFICER_REMEMBER.RECORD_STATUS = 'Y' ")
        sb.AppendLine("ORDER BY REMEMBER_PAYMENT DESC")
        Return Excute(sb.ToString)
    End Function

    Public Function InsertOfficerRemember(ByVal info As Entity.OfficerRemember) As Boolean
        info.remember_id = GetOfficerRememberID()
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_REMEMBER")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function UpdateOfficerRemember(ByVal info As Entity.OfficerRemember) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_REMEMBER", "TO_CHAR(REMEMBER_ID)='" & info.remember_id & "'")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function GetOfficerRememberID() As Decimal
        Dim REMID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select REMEMBER_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_REMEMBER ")
        sb.AppendLine("ORDER BY REMEMBER_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            REMID = Convert.ToDecimal(dt.Rows(0)("REMEMBER_ID")) + 1
        Else
            REMID = 1
        End If
        Return REMID
    End Function

    Public Shared Function GetOfficerExpiredDate(ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO ")
        sb.AppendLine("WHERE TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) = '" & officerId & "' ")
        Return Excute(sb.ToString)
    End Function

    Public Function UpdateOfficerExpiredDate(ByVal officerId As String, ByVal expireddate As String, ByVal upd_user_id As String) As Boolean
        Dim sb As New Text.StringBuilder
        sb.AppendLine("UPDATE " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO SET ")
        sb.AppendLine("RNWM_MAIN_OFFICER_INFO.OFFCIER_EXPIRD_DATE = TO_DATE('" & expireddate & "','YYYYMMDD')")
        sb.AppendLine(",RNWM_MAIN_OFFICER_INFO.UPD_USER_ID = '" & upd_user_id & "' ")
        sb.AppendLine(",RNWM_MAIN_OFFICER_INFO.UPD_DATE=sysdate")
        sb.AppendLine("WHERE TO_CHAR(RNWM_MAIN_OFFICER_INFO.OFFICER_ID) = '" & officerId & "' ")
        If connOra.ExecuteNonQuery(sb.ToString) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

#End Region

#Region "Officer Attach"

    Public Shared Function GetOfficerAttach(ByVal officerId As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH ")
        sb.AppendLine("WHERE RNWM_MAIN_OFFICER_ATTACH.OFFICER_ID = '" & officerId & "' ")
        sb.AppendLine("AND RNWM_MAIN_OFFICER_ATTACH.RECORD_STATUS = 'Y' ")
        sb.AppendLine("ORDER BY ATTACH_FILE_NAME ASC")
        Return Excute(sb.ToString)
    End Function

    Public Shared Function GetOfficerAttachById(ByVal attachid As String) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH ")
        sb.AppendLine("WHERE TO_CHAR(RNWM_MAIN_OFFICER_ATTACH.ATTACH_ID) = '" & attachid & "' ")
        Return Excute(sb.ToString)
    End Function

    Public Function InsertOfficerAttach(ByVal info As Entity.OfficerAttach) As Boolean
        info.attach_id = GetOfficerAttachID()
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function UpdateOfficerAttach(ByVal info As Entity.OfficerAttach) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH", "TO_CHAR(ATTACH_ID)='" & info.attach_id & "'")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function UpdateOfficerAttachByPath(ByVal info As Entity.OfficerAttach) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH", "ATTACH_FILE_PATH='" & info.attach_file_path & "' AND RNWM_MAIN_OFFICER_ATTACH.RECORD_STATUS = 'Y'")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function DeleteOfficerAttach(ByVal AttachId As String) As Boolean
        Dim strSql As String
        strSql = "DELETE FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH WHERE RNWM_MAIN_OFFICER_ATTACH.ATTACH_ID=" & AttachId
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function GetOfficerAttachID() As Decimal
        Dim oID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select ATTACH_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_ATTACH ")
        sb.AppendLine("ORDER BY ATTACH_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            oID = Convert.ToDecimal(dt.Rows(0)("ATTACH_ID")) + 1
        Else
            oID = 1
        End If
        Return oID
    End Function

#End Region

End Class
